package org.example.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import lombok.extern.slf4j.Slf4j;
import org.example.dto.LoanDataExcelDTO;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * Excel过滤去重服务
 * 功能：
 * 1. 删除还款状态为-1的订单
 * 2. 按订单号和期数去重
 * 3. 保持原有字段顺序导出
 */
@Slf4j
@Service
public class ExcelFilterDedupService {

    /**
     * 处理Excel文件：过滤和去重
     * @param file 输入的Excel文件
     * @param response HTTP响应
     * @throws IOException IO异常
     */
    public void processExcel(MultipartFile file, HttpServletResponse response) throws IOException {
        log.info("开始处理Excel文件: {}", file.getOriginalFilename());
        
        // 用于存储去重后的数据，key为"订单号_期数"
        Map<String, LoanDataExcelDTO> dedupMap = new ConcurrentHashMap<>();
        final int[] totalCount = {0};
        final int[] filteredCount = {0};
        final int[] duplicateCount = {0};

        // 读取Excel文件
        EasyExcel.read(file.getInputStream(), LoanDataExcelDTO.class, new ReadListener<LoanDataExcelDTO>() {
            @Override
            public void invoke(LoanDataExcelDTO data, AnalysisContext context) {
                totalCount[0]++;
                
                // 1. 过滤：删除还款状态为-1的订单
                if (data.getRepayStatus() != null && data.getRepayStatus() == -1) {
                    filteredCount[0]++;
                    log.debug("过滤掉还款状态为-1的订单: 订单号={}, 期数={}", 
                        data.getOrderNumber(), data.getInstallmentCount());
                    return;
                }
                
                // 2. 去重：按订单号和期数去重
                String dedupKey = generateDedupKey(data.getOrderNumber(), data.getInstallmentCount());
                LoanDataExcelDTO existingData = dedupMap.get(dedupKey);
                
                if (existingData != null) {
                    duplicateCount[0]++;
                    log.debug("发现重复数据: 订单号={}, 期数={}", 
                        data.getOrderNumber(), data.getInstallmentCount());
                    
                    // 保留最新的数据（可以根据需要调整保留策略）
                    if (shouldKeepNewData(existingData, data)) {
                        dedupMap.put(dedupKey, data);
                    }
                } else {
                    dedupMap.put(dedupKey, data);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("Excel读取完成 - 总行数: {}, 过滤行数: {}, 重复行数: {}, 最终行数: {}", 
                    totalCount[0], filteredCount[0], duplicateCount[0], dedupMap.size());
            }
        }).sheet().doRead();

        // 3. 导出处理后的数据
        exportProcessedData(dedupMap, response, file.getOriginalFilename());
    }

    /**
     * 生成去重键
     * @param orderNumber 订单号
     * @param installmentCount 期数
     * @return 去重键
     */
    private String generateDedupKey(String orderNumber, Integer installmentCount) {
        if (orderNumber == null) {
            orderNumber = "";
        }
        if (installmentCount == null) {
            installmentCount = 0;
        }
        return orderNumber + "_" + installmentCount;
    }

    /**
     * 判断是否保留新数据
     * 这里可以根据业务需求调整保留策略
     * 当前策略：保留放款时间较新的数据
     * @param existingData 已存在的数据
     * @param newData 新数据
     * @return 是否保留新数据
     */
    private boolean shouldKeepNewData(LoanDataExcelDTO existingData, LoanDataExcelDTO newData) {
        // 如果新数据的放款时间更新，则保留新数据
        if (existingData.getLoanTime() != null && newData.getLoanTime() != null) {
            return newData.getLoanTime().isAfter(existingData.getLoanTime());
        }
        
        // 如果放款时间相同或为空，保留新数据（后读取的）
        return true;
    }

    /**
     * 导出处理后的数据
     * @param dedupMap 去重后的数据
     * @param response HTTP响应
     * @param originalFilename 原始文件名
     * @throws IOException IO异常
     */
    private void exportProcessedData(Map<String, LoanDataExcelDTO> dedupMap, 
                                   HttpServletResponse response, 
                                   String originalFilename) throws IOException {
        
        // 转换为List并保持原有顺序
        List<LoanDataExcelDTO> dataList = new ArrayList<>(dedupMap.values());
        
        // 按订单号和期数排序，保持数据的有序性
        dataList.sort((a, b) -> {
            int orderCompare = Objects.compare(
                a.getOrderNumber(), 
                b.getOrderNumber(), 
                Comparator.nullsLast(String::compareTo)
            );
            if (orderCompare != 0) {
                return orderCompare;
            }
            return Objects.compare(
                a.getInstallmentCount(), 
                b.getInstallmentCount(), 
                Comparator.nullsLast(Integer::compareTo)
            );
        });

        // 设置响应头
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String processedFilename = generateProcessedFilename(originalFilename, timestamp);
        
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", 
            "attachment; filename=" + URLEncoder.encode(processedFilename, StandardCharsets.UTF_8));

        // 导出Excel
        EasyExcel.write(response.getOutputStream(), LoanDataExcelDTO.class)
            .sheet("处理后的数据")
            .doWrite(dataList);
            
        log.info("Excel导出完成，共导出 {} 条数据", dataList.size());
    }

    /**
     * 生成处理后的文件名
     * @param originalFilename 原始文件名
     * @param timestamp 时间戳
     * @return 处理后的文件名
     */
    private String generateProcessedFilename(String originalFilename, String timestamp) {
        if (originalFilename == null) {
            return "processed_data_" + timestamp + ".xlsx";
        }
        
        String nameWithoutExt = originalFilename;
        String extension = ".xlsx";
        
        int lastDotIndex = originalFilename.lastIndexOf('.');
        if (lastDotIndex > 0) {
            nameWithoutExt = originalFilename.substring(0, lastDotIndex);
            extension = originalFilename.substring(lastDotIndex);
        }
        
        return nameWithoutExt + "_去重_" + timestamp + extension;
    }

    /**
     * 获取处理统计信息
     * @param file 输入的Excel文件
     * @return 处理统计信息
     */
    public Map<String, Object> getProcessStatistics(MultipartFile file) throws IOException {
        Map<String, Object> statistics = new HashMap<>();
        
        final int[] totalCount = {0};
        final int[] filteredCount = {0};
        final Set<String> uniqueKeys = new HashSet<>();
        final Set<String> duplicateKeys = new HashSet<>();

        EasyExcel.read(file.getInputStream(), LoanDataExcelDTO.class, new ReadListener<LoanDataExcelDTO>() {
            @Override
            public void invoke(LoanDataExcelDTO data, AnalysisContext context) {
                totalCount[0]++;
                
                // 统计过滤数量
                if (data.getRepayStatus() != null && data.getRepayStatus() == -1) {
                    filteredCount[0]++;
                    return;
                }
                
                // 统计去重数量
                String dedupKey = generateDedupKey(data.getOrderNumber(), data.getInstallmentCount());
                if (!uniqueKeys.add(dedupKey)) {
                    duplicateKeys.add(dedupKey);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 统计完成
            }
        }).sheet().doRead();

        statistics.put("totalCount", totalCount[0]);
        statistics.put("filteredCount", filteredCount[0]);
        statistics.put("duplicateCount", duplicateKeys.size());
        statistics.put("finalCount", uniqueKeys.size());
        
        return statistics;
    }

    /**
     * 处理Excel文件并导出到指定目录
     * @param file 输入的Excel文件
     * @param outputDir 输出目录路径
     * @return 导出文件的完整路径
     * @throws IOException IO异常
     */
    public String processExcelToDirectory(MultipartFile file, String outputDir) throws IOException {
        log.info("开始处理Excel文件: {}，输出目录: {}", file.getOriginalFilename(), outputDir);
        
        // 创建输出目录
        Path outputPath = Paths.get(outputDir);
        if (!Files.exists(outputPath)) {
            Files.createDirectories(outputPath);
            log.info("创建输出目录: {}", outputDir);
        }
        
        // 用于存储去重后的数据，key为"订单号_期数"
        Map<String, LoanDataExcelDTO> dedupMap = new ConcurrentHashMap<>();
        final int[] totalCount = {0};
        final int[] filteredCount = {0};
        final int[] duplicateCount = {0};

        // 读取Excel文件
        EasyExcel.read(file.getInputStream(), LoanDataExcelDTO.class, new ReadListener<LoanDataExcelDTO>() {
            @Override
            public void invoke(LoanDataExcelDTO data, AnalysisContext context) {
                totalCount[0]++;
                
                // 1. 过滤：删除还款状态为-1的订单
                if (data.getRepayStatus() != null && data.getRepayStatus() == -1) {
                    filteredCount[0]++;
                    log.debug("过滤掉还款状态为-1的订单: 订单号={}, 期数={}", 
                        data.getOrderNumber(), data.getInstallmentCount());
                    return;
                }
                
                // 2. 去重：按订单号和期数去重
                String dedupKey = generateDedupKey(data.getOrderNumber(), data.getInstallmentCount());
                LoanDataExcelDTO existingData = dedupMap.get(dedupKey);
                
                if (existingData != null) {
                    duplicateCount[0]++;
                    log.debug("发现重复数据: 订单号={}, 期数={}", 
                        data.getOrderNumber(), data.getInstallmentCount());
                    
                    // 保留最新的数据（可以根据需要调整保留策略）
                    if (shouldKeepNewData(existingData, data)) {
                        dedupMap.put(dedupKey, data);
                    }
                } else {
                    dedupMap.put(dedupKey, data);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("Excel读取完成 - 总行数: {}, 过滤行数: {}, 重复行数: {}, 最终行数: {}", 
                    totalCount[0], filteredCount[0], duplicateCount[0], dedupMap.size());
            }
        }).sheet().doRead();

        // 3. 导出处理后的数据到指定目录
        return exportProcessedDataToDirectory(dedupMap, outputDir, file.getOriginalFilename());
    }

    /**
     * 导出处理后的数据到指定目录
     * @param dedupMap 去重后的数据
     * @param outputDir 输出目录
     * @param originalFilename 原始文件名
     * @return 导出文件的完整路径
     * @throws IOException IO异常
     */
    private String exportProcessedDataToDirectory(Map<String, LoanDataExcelDTO> dedupMap, 
                                                String outputDir, 
                                                String originalFilename) throws IOException {
        
        // 转换为List并保持原有顺序
        List<LoanDataExcelDTO> dataList = new ArrayList<>(dedupMap.values());
        
        // 按订单号和期数排序，保持数据的有序性
        dataList.sort((a, b) -> {
            int orderCompare = Objects.compare(
                a.getOrderNumber(), 
                b.getOrderNumber(), 
                Comparator.nullsLast(String::compareTo)
            );
            if (orderCompare != 0) {
                return orderCompare;
            }
            return Objects.compare(
                a.getInstallmentCount(), 
                b.getInstallmentCount(), 
                Comparator.nullsLast(Integer::compareTo)
            );
        });

        // 生成输出文件名
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String processedFilename = generateProcessedFilename(originalFilename, timestamp);
        
        // 构建完整的文件路径
        Path outputPath = Paths.get(outputDir, processedFilename);
        
        // 导出Excel到指定目录
        EasyExcel.write(outputPath.toFile(), LoanDataExcelDTO.class)
            .sheet("处理后的数据")
            .doWrite(dataList);
            
        log.info("Excel导出完成，共导出 {} 条数据到文件: {}", dataList.size(), outputPath.toString());
        
        return outputPath.toString();
    }
}
